-- EXEC SP_CALCULATE_LOT_COST
ALTER PROC SP_CALCULATE_LOT_COST 
AS
BEGIN
	DECLARE @CCT_WAFER_COST NUMERIC(18,4)
	DECLARE @CCT_DIE_COST	NUMERIC(18,4)
	DECLARE @CCT_ASSM_COST	NUMERIC(18,4)
	DECLARE @CCT_TEST_COST	NUMERIC(18,4)
	DECLARE @CCT_FG_COST	NUMERIC(18,4)
	DECLARE @CCT_TR_COST	NUMERIC(18,4)
	DECLARE @LOT_ID			BIGINT
	DECLARE @PROCESSSTEP_ID 	BIGINT
	DECLARE @TR_PROCESSSTEP_ID 	BIGINT
	DECLARE @PS_TYPE		VARCHAR(50) 
	DECLARE @DATE_IN		DATETIME
	DECLARE @QTY_IN			INT
	DECLARE @BALANCE_QTY		INT
	
	DECLARE LOT_CURSOR CURSOR FOR SELECT DISTINCT PS.PROCESSSTEP_ID, PS.PS_TYPE, CCT.WAFER_COST, CCT.DIE_COST, CCT.ASSM_COST, CCT.TEST_COST, CCT.FG_COST, CCT.TR_COST
	FROM LOT_TABLE LT, PROCESS_STEP PS, PRODUCT P, COST_CODE_TABLE CCT WHERE 
	LT.PROCESSSTEP_ID = PS.PROCESSSTEP_ID AND PS.PROCESSSTEP_ID = P.PROCESSSTEP_ID AND P.COST_CODE = CCT.COST_CODE AND
	( Lt.Lot_Current_Status = 'Running' OR Lt.Lot_Current_Status = 'Hold' ) AND P.Cost_Code IS NOT NULL AND CCT.YEAR = YEAR( CURRENT_TIMESTAMP ) 
	AND CCT.Qtr = ( SELECT CQC_Quarter_No FROM Company_Quarterly_Calendar WHERE CURRENT_TIMESTAMP >= CQC_Start_Date AND CURRENT_TIMESTAMP <= CQC_END_DATE )

	OPEN LOT_CURSOR
	FETCH NEXT FROM LOT_CURSOR INTO @PROCESSSTEP_ID, @PS_TYPE, @CCT_WAFER_COST, @CCT_DIE_COST, @CCT_ASSM_COST, @CCT_TEST_COST, @CCT_FG_COST, @CCT_TR_COST
	WHILE @@FETCH_STATUS = 0
	BEGIN
		IF ( @PS_TYPE = 'FAB' OR @PS_TYPE = 'Fab Option' )
		BEGIN
			UPDATE LOT_TABLE SET Lot_Cost = @CCT_WAFER_COST, LOT_CUMMULATIVE_COST = ( Wafer_Qty_In * ISNULL( @CCT_WAFER_COST, 0.0 ) ) WHERE ProcessStep_ID = @PROCESSSTEP_ID AND ( Lot_Current_Status = 'Running' OR Lot_Current_Status = 'Hold' )
		END
		ELSE IF( @PS_TYPE = 'DIE BANK' OR @PS_TYPE = 'ASSEMBLY' )
		BEGIN
			UPDATE LOT_TABLE SET Lot_Cost = @CCT_DIE_COST, LOT_CUMMULATIVE_COST = ( QTY_IN * ISNULL( @CCT_DIE_COST, 0.0 ) ) WHERE ProcessStep_ID = @PROCESSSTEP_ID AND ( Lot_Current_Status = 'Running' OR Lot_Current_Status = 'Hold' )
		END
		ELSE IF( @PS_TYPE = 'FT' )
		BEGIN
			UPDATE LOT_TABLE SET Lot_Cost = @CCT_ASSM_COST, LOT_CUMMULATIVE_COST = ( QTY_IN * ISNULL( @CCT_ASSM_COST, 0.0 ) ) WHERE ProcessStep_ID = @PROCESSSTEP_ID AND ( Lot_Current_Status = 'Running' OR Lot_Current_Status = 'Hold' )
		END
		ELSE IF( @PS_TYPE = 'FPO' OR @PS_TYPE = 'Tape & Reel' )
		BEGIN
			IF( @CCT_TEST_COST IS NOT NULL AND @CCT_TEST_COST != 0.0 )
			BEGIN
				UPDATE LOT_TABLE SET Lot_Cost = @CCT_TEST_COST, LOT_CUMMULATIVE_COST = ( QTY_IN * ISNULL( @CCT_TEST_COST, 0.0 ) ) WHERE ProcessStep_ID = @PROCESSSTEP_ID AND ( Lot_Current_Status = 'Running' OR Lot_Current_Status = 'Hold' )
			END
			ELSE
			BEGIN
				UPDATE LOT_TABLE SET Lot_Cost = @CCT_ASSM_COST, LOT_CUMMULATIVE_COST = ( QTY_IN * ISNULL( @CCT_ASSM_COST, 0.0 ) ) WHERE ProcessStep_ID = @PROCESSSTEP_ID AND ( Lot_Current_Status = 'Running' OR Lot_Current_Status = 'Hold' )
			END			
		END
		ELSE IF( @PS_TYPE = 'FG' )
		BEGIN
			---SELECT @QTY_IN = ISNULL( QTY_IN, 0 ) FROM LOT_TABLE WHERE LOT_ID = @LOT_ID

			SELECT TOP 1 @TR_PROCESSSTEP_ID = PROCESSSTEP_ID FROM FN_GET_PROCESS_FLOW_BACKWARD_BY_ID( @PROCESSSTEP_ID ) WHERE PS_TYPE = 'Tape & Reel'
			IF( @TR_PROCESSSTEP_ID IS NULL )
			BEGIN
				UPDATE LOT_TABLE SET Lot_Cost = @CCT_FG_COST, LOT_CUMMULATIVE_COST = ( ( ISNULL ( QTY_IN, 0 ) ) * @CCT_FG_COST ) WHERE ProcessStep_ID = @PROCESSSTEP_ID AND ( Lot_Current_Status = 'Running' OR Lot_Current_Status = 'Hold' )
			END
			ELSE
			BEGIN
				UPDATE LOT_TABLE SET Lot_Cost = @CCT_TR_COST, LOT_CUMMULATIVE_COST = ( ( ISNULL ( QTY_IN, 0 ) ) * @CCT_TR_COST ) WHERE ProcessStep_ID = @PROCESSSTEP_ID AND ( Lot_Current_Status = 'Running' OR Lot_Current_Status = 'Hold' )
			END
		END

		SET @CCT_DIE_COST	= NULL 
		SET @CCT_ASSM_COST	= NULL 
		SET @CCT_TEST_COST	= NULL 
		SET @CCT_FG_COST	= NULL 
		SET @CCT_TR_COST	= NULL 
		
		SET @TR_PROCESSSTEP_ID = NULL

		FETCH NEXT FROM LOT_CURSOR INTO @PROCESSSTEP_ID, @PS_TYPE, @CCT_WAFER_COST, @CCT_DIE_COST, @CCT_ASSM_COST, @CCT_TEST_COST, @CCT_FG_COST, @CCT_TR_COST
	END	
	CLOSE LOT_CURSOR
	DEALLOCATE LOT_CURSOR
END 